# -*- coding: utf-8 -*-
import MySQLdb
import MySQLdb.cursors
import sys
import cvs2PersionListManagerCompare
import cvs2PersionDetailManagerCompare

reload(sys)
sys.setdefaultencoding('utf8')

"""
author：zhangqing
time = 2018-6-25
function: 数据初始化对比：cvs1.0与cvs2.0 人物列表职业生涯对比（公司全称、职位）
"""

#用此变量存放1.0和2.0数据库符合条件的persionId
#flagType==1 代表人物列表职业生涯
def searchCvs1Persion(flagType):
    db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)

    cursor = db.cursor()

    sql = "SELECT COUNT(a.id) a FROM datacenter_online_20180609.dc_personage a ORDER BY a.id DESC "
    cursor.execute(sql)
    resultsCount = cursor.fetchone()
    pageCount = resultsCount["a"]/50
    for index in range(pageCount):
    # for index in range(13,668):
        limitStart = index * 50
        # limitStart = 1623
        print("limitStart====================================================",limitStart)

        sql = "SELECT a.cn_name, a.en_name, a.sex, a.photograph, if(a.education = 1, 5, if(a.education = 2, 4, if(a.education = 3, 3, if(a.education = 4, 2, null)))) AS education," \
              " a.remark, a.country, a.province, a.city, a.cn_description, a.en_description, a.quality_status, a.info_status," \
              "a.creater_id AS create_id, a.create_time, a.modifier_id AS modify_id, a.modify_time,a.`status`, a.id " \
              "FROM datacenter_online_20180609.dc_personage a ORDER BY a.id DESC limit %d,50 "%limitStart



        # sql = "SELECT a.cn_name, a.en_name, a.sex, a.photograph, if(a.education = 1, 5, if(a.education = 2, 4, if(a.education = 3, 3, if(a.education = 4, 2, null)))) AS education," \
        #           " a.remark, a.country, a.province, a.city, a.cn_description, a.en_description, a.quality_status, a.info_status," \
        #           "a.creater_id AS create_id, a.create_time, a.modifier_id AS modify_id, a.modify_time,a.`status`, a.id " \
        #           "FROM datacenter_online_20180609.dc_personage a WHERE a.cn_name = '田宇'"

        # sql = "SELECT a.cn_name, a.en_name, a.sex, a.photograph, if(a.education = 1, 5, if(a.education = 2, 4, if(a.education = 3, 3, if(a.education = 4, 2, null)))) AS education," \
        #       " a.remark, a.country, a.province, a.city, a.cn_description, a.en_description, a.quality_status, a.info_status," \
        #       "a.creater_id AS create_id, a.create_time, a.modifier_id AS modify_id, a.modify_time,a.`status`, a.id " \
        #       "FROM datacenter_online_20180609.dc_personage a WHERE a.cn_name='吴世春' ORDER BY a.id "

        cursor.execute(sql)
        results1 = cursor.fetchall()

        #查询1.0和2.0数据库符合条件的persionId
        persionIdList = searchCvs2Persion(results1)
        #flagType==1 人物列表职业生涯对比
        if flagType==1:
            cvs2PersionListManagerCompare.searchCvs1PersionManager(persionIdList)

        #flagType==2 人物详情页职业生涯对比
        elif flagType==2:
            cvs2PersionDetailManagerCompare.searchCvs1PersionManager(persionIdList)

    db.close()

def searchCvs2Persion(results1):
    persionIdList = []
    db = MySQLdb.connect("rm-2ze9cx91355z4mk9hpo.mysql.rds.aliyuncs.com", "cvs2.0_read_only", "aaKcKA6iDXyJVeF6",
                         "cms_plus", charset='utf8',cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    for index in range(len(results1)):

        cn_name1 = results1[index].get(u"cn_name")
        en_name1 = results1[index].get(u"en_name")
        print("en_name1=",en_name1)
        sex1 = results1[index].get(u"sex")
        country1 = results1[index].get(u"country")
        province1 = results1[index].get(u"province")
        city1 = results1[index].get(u"city")
        cn_description1 = results1[index].get(u"cn_description")
        en_description1 = results1[index].get(u"en_description")

        if cn_name1 ==None and en_name1==None:
            continue

        sqlParam  = tuple([])
        sql = "SELECT a.cn_name, a.en_name, a.sex, a.photograph, a.education," \
              " a.remark, a.country, a.province, a.city, a.cn_description, a.en_description, a.quality_status, a.info_status, " \
              "a.create_id , a.create_time, a.modify_id, a.modify_time, a.status, a.id, a.enable_search as beizhu " \
              "FROM cms_plus.tbl_person a WHERE a.`status`=1"

        if cn_name1!=None:
            sql += " AND a.cn_name = '%s'"
            sqlParam += tuple([cn_name1])

        if en_name1!=None:
            en_name1 = MySQLdb.escape_string(en_name1)
            sql += " AND a.en_name = '%s'"
            sqlParam += tuple([en_name1])
        else:
            sql += " AND a.en_name is null"

        if country1!=None:
            sql += " AND a.country = '%s'"
            sqlParam += tuple([country1])

        if province1!=None:
            sql += " AND a.province = '%s'"
            sqlParam += tuple([province1])

        if city1!=None:
            sql += " AND a.city = '%s'"
            sqlParam += tuple([city1])

        if sex1!=None:
            sql += " AND a.sex = '%s'"
            sqlParam += tuple([sex1])

        if cn_description1!=None:
             cn_description1 = MySQLdb.escape_string(cn_description1)
             sql += " AND a.cn_description = '%s'"
             sqlParam += tuple([cn_description1])
        else:
            sql += " AND a.cn_description is null"

        if en_description1!=None:
            en_description1 = MySQLdb.escape_string(en_description1)
            sql += " AND a.en_description = '%s'"
            sqlParam += tuple([en_description1])
        else:
            sql += " AND a.en_description is null"

        cursor.execute(sql %(sqlParam))
        print("cvs2.0sql===",sql %(sqlParam))
        results2 = cursor.fetchall()

        for index2 in range(len(results2)):

            cn_name2 = results2[index2].get(u"cn_name")
            en_name2 = results2[index2].get(u"en_name")
            country2 = results2[index2].get(u"country")
            province2 = results2[index2].get(u"province")
            city2 = results2[index2].get(u"city")
            cn_description2 = results2[index2].get(u"cn_description")
            if cn_description2!=None:
                cn_description2 = MySQLdb.escape_string(cn_description2)
            en_description2 = results2[index2].get(u"en_description")
            if en_description1!=None:
                en_description2 = MySQLdb.escape_string(en_description2)

            flag = (cn_name1 == cn_name2 and en_name1 == en_name2 and country1 == country2 and province1 == province2 and city1 == city2 and cn_description1 == cn_description2 and en_description1 == en_description2)
            if flag == True:
                persionIdDict = {}
                persionIdDict["persionId1"] = results1[index].get(u"id")
                persionIdDict["persionId2"] = results2[index2].get(u"id")
                persionIdList.append(persionIdDict)
    return persionIdList

    db.close()

